﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using ModelLayer;

namespace DBLayer
{
    public class PersonDBClient : IPersonDB
    {
        public PersonDBClient()
        {
        }
        private static SqlCommand dbCmd;

        public int InsertPerson(Person p)
        {
            string sql = "";
            if (p.GetType().Equals(typeof(Person)))
            {
                sql = "insert into Person(fname, lname, telephone) " +
                   "Values('" +
                   p.FName + "', '" +
                   p.LName + "', '" +
                   p.Telephone + "')";
            }
            else if (p.GetType().Equals(typeof(Member)))
            {
                Member m = (Member)p;
                sql = "insert into Person(fname, lname, telephone, email, address, zipcode) " +
                   "Values('" +
                   m.FName + "', '" +
                   m.LName + "', '" +
                   m.Telephone + "', '" +
                   m.Email + "', '" +
                   m.Address + "', " +
                   m.ZipCode + ")";
            }

            return AccessDBSQLClient.ExecuteQuery(sql);
        }


        public Person GetPerson(int id)
        {
            string sql = "SELECT * FROM Person WHERE id = " + id;

            dbCmd = AccessDBSQLClient.GetDbCommand(sql);
            IDataReader dbReader;

            dbReader = dbCmd.ExecuteReader();

            Person p;
            if (dbReader.Read())
            {
                p = new Person(dbReader["fname"].ToString(), dbReader["lname"].ToString(), dbReader["telephone"].ToString());
                p.Id = Convert.ToInt32(dbReader["id"]);
            }
            else
            {
                p = null;
            }

            dbReader.Close();
            dbCmd.Connection.Close();

            return p;
        }

        public Person GetPerson(string tlf)
        {
            string sql = "SELECT * FROM Person WHERE Telephone = " + "'" + tlf + "'";

            dbCmd = AccessDBSQLClient.GetDbCommand(sql);
            IDataReader dbReader;

            dbReader = dbCmd.ExecuteReader();

            Person p;
            if (dbReader.Read())
            {
                p = new Person(dbReader["fname"].ToString(), dbReader["lname"].ToString(), dbReader["telephone"].ToString());
            }
            else
            {
                p = null;
            }

            dbReader.Close();
            dbCmd.Connection.Close();

            return p;
        }

        public Member GetMember(int id)
        {
            string sql = "SELECT * FROM Person WHERE id = " + id;

            dbCmd = AccessDBSQLClient.GetDbCommand(sql);
            IDataReader dbReader;

            dbReader = dbCmd.ExecuteReader();

            Member m;
            if (dbReader.Read())
            {
                m = new Member(
                    dbReader["fname"].ToString(),
                    dbReader["lname"].ToString(),
                    dbReader["telephone"].ToString(),
                    dbReader["email"].ToString(),
                    dbReader["address"].ToString(),
                    Convert.ToInt32(dbReader["zipcode"].ToString()));
            }
            else
            {
                m = null;
            }

            dbReader.Close();
            dbCmd.Connection.Close();

            return m;
        }

        public int DeletePerson(int id)
        {
            string sql = "DELETE * FROM Person WHERE id = " + id;

            return AccessDBSQLClient.ExecuteQuery(sql);
        }

        public int UpdatePerson(Person p)
        {
            string sql = "UPDATE `Person` SET `Fname` = '" + p.FName + "', `LName` = '" + p.LName + "', `Telephone` = '" + p.Telephone + " WHERE `id` = " + p.Id;

            return AccessDBSQLClient.ExecuteQuery(sql);
        }
    }
}
